const mysql = require("mysql");
const config = require("./config");
const { debug } = require("../utils/constant"); //添加调试开关
const { isObject } = require("../utils/index");

// 返回一个数据库连接对象
function connect() {
  return mysql.createConnection({
    host: config.host,
    user: config.user,
    password: config.password,
    database: config.database,
    multipleStatements: true, // 有潜在风险，sql注入
  });
}

function querySql(sql) {
  const conn = connect();
  debug && console.log(sql); // 打印调试信息
  return new Promise((resolve, reject) => {
    try {
      conn.query(sql, (err, results) => {
        if (err) {
          debug && console.log("查询失败：", JSON.stringify(err));
          reject(err);
        } else {
          debug && console.log("查询成功：", JSON.stringify(results));
          resolve(results);
        }
      });
    } catch (e) {
      reject(e);
    } finally {
      conn.end(); //关闭连接
    }
  });
}

// 返回1条记录的查询
function queryOne(sql) {
  return new Promise((resolve, reject) => {
    querySql(sql)
      .then((results) => {
        if (results && results.length > 0) {
          resolve(results[0]); // 回调处理返回结果
        } else {
          resolve(null);
        }
      })
      .catch((err) => {
        reject(err);
      });
  });
}

//对象，表名；对象保存入表名
function insert(data, tableName) {
  return new Promise((resolve, reject) => {
    // 判断是否为对象
    if (!isObject(data)) {
      reject(new Error("插入数据库失败，插入数据非对象"));
    } else {
      const keys = [];
      const values = [];
      Object.keys(data).forEach((key) => {
        // 只处理非继承的property
        if (data.hasOwnProperty(key)) {
          keys.push(`\`${key}\``); // 防止关键字冲突 添加``
          values.push(`'${data[key]}'`);
        }
      });
      //生成sql语句
      if (keys.length > 0 && values.length > 0) {
        let sql = `insert into \`${tableName}\` (`;
        const keysString = keys.join(",");
        const valuesString = values.join(",");
        sql = `${sql}${keysString}) values (${valuesString})`;
        debug && console.log(sql);
        const conn = connect();
        try {
          conn.query(sql, (err, result) => {
            if (err) {
              reject(err);
            } else {
              resolve(result);
            }
          });
        } catch (e) {
          reject(e);
        } finally {
          conn.end();
        }
      } else {
        reject(new Error("插入数据库失败，对象中无任何属性"));
      }
    }
  });
}

function update(data, tableName, where) {
  return new Promise((resolve, reject) => {
    // 判断是否为对象
    if (!isObject(data)) {
      reject(new Error("插入数据库失败，插入数据非对象"));
    } else {
      const entry = [];
      Object.keys(data).forEach((key) => {
        // 只处理非继承的property
        if (data.hasOwnProperty(key)) {
          entry.push(`\`${key}\`='${data[key]}'`);
        }
      });
      if (entry.length > 0) {
        let sql = `update \`${tableName}\` set`;
        sql = `${sql} ${entry.join(",")} ${where}`;
        debug && console.log(sql);
        //执行
        const conn = connect();
        try {
          conn.query(sql, (err, result) => {
            if (err) {
              reject(err);
            } else {
              resolve(result);
            }
          });
        } catch (e) {
          reject(e);
        } finally {
          conn.end();
        }
      } else {
        reject(new Error("插入数据库失败，对象中无任何属性"));
      }
    }
  });
}

module.exports = {
  querySql,
  queryOne,
  insert,
  update,
};
